package com.sx.utils;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DBUtils {

  /**
   * 获取数据库连接
   * @return 数据库连接
   */
  public static Connection getConnection()  {

    try {
//      InputStream ins = DBUtils.class.getClass().getResourceAsStream("/jdbc.properties");
//      Properties pros = new Properties();
//      pros.load(ins);
//
//      String username = (String) pros.get("jdbc.username");
//      String password = (String) pros.get("jdbc.password");
//      String url = (String) pros.get("jdbc.url");
//      String driver = (String) pros.get("jdbc.driver");

      Class.forName("com.mysql.jdbc.Driver");

      Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/playmanage?characterEncoding=utf8", "root", "sx20010713");

      return connection;
    } catch (SQLException | ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

    return null;
  }

  /**
   * 关闭连接
   * @param conn 要关闭的连接
   */
  public static void CloseConnection(Connection conn) {

    if (conn != null) {
      try {
        conn.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }


  /**
   * 增删改操作
   * @param conn	连接
   * @param sql	执行的sql
   * @param args	填充的条件
   */
  public static void synthesize(Connection conn , String sql , Object ... args){

    try {
      PreparedStatement prepStatement = conn.prepareStatement(sql);

      for (int i = 0 ;i < args.length ;i++) {
        prepStatement.setObject(i + 1, args[i]);
      }

      prepStatement.execute();

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

  }

  /**
   * 查询
   * @param clazz	元素的Class对象
   * @param conn	连接
   * @param sql	sql语句
   * @param args	填充条件
   * @param <T>	List中元素的类型
   * @return 返回查询值
   */
  public static <T> List<T> select(Class<T> clazz ,Connection conn , String sql , Object ... args){

    try {
      PreparedStatement preStatement = conn.prepareStatement(sql);

      for (int i = 0 ;i < args.length ;i++) {
        preStatement.setObject(i + 1, args[i]);
      }

      ResultSet resultSet = preStatement.executeQuery();

      ResultSetMetaData metaData = resultSet.getMetaData();

      // 获取数据列数
      int columnCount = metaData.getColumnCount();

      List<T> list = new ArrayList<T>();

      // 填充数据
      while (resultSet.next()) {
        T t = clazz.newInstance();
        for (int i = 0 ; i < columnCount ;i++) {
          // 获取数据
          Object columValue = resultSet.getObject(i + 1);
          // 获取列名
          String columnLabel = metaData.getColumnLabel(i + 1);

          // 获取属性
          Field field = clazz.getDeclaredField(columnLabel);

          field.setAccessible(true);
          field.set(t, columValue);
        }
        list.add(t);
      }

      return list;

    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (InstantiationException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IllegalAccessException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (NoSuchFieldException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SecurityException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

    return null;

  }

}
